package du.contact2018.database;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.icu.text.IDNA;
import android.text.TextUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;

import du.contact2018.model.InfoBean;

public class SqlMethod {

    Context context;
    SQLHelper dbOpenHelper;
    SQLiteDatabase db;

/* // 数据表，各个字段，无用--------------------参考之用-------------------
    private int _id;
    private String name;//姓名
    private String unit;//单位
    private String dept;//办公室，工作地点，工作房间

    private ArrayList<String> titles = new ArrayList<>();// 头衔:领导，行政，教师，职工等
    private ArrayList<String> officePhones = new ArrayList<>();//办公室电话
    private ArrayList<String> homePhones = new ArrayList<>();//宅电
    private ArrayList<String> mobilePhones = new ArrayList<>();//手机
    private ArrayList<String> addresses = new ArrayList<>();//住址

    private ArrayList<String> QQs = new ArrayList<>(); //  QQ
    private ArrayList<String> weixins = new ArrayList<>(); //  微信
    private ArrayList<String> emails = new ArrayList<>(); //   邮箱
    private ArrayList<String> teams = new ArrayList<>(); //   组属，团体
*/


    public SqlMethod(Context context) {
        this.context = context;
        this.dbOpenHelper = new SQLHelper(context);
        db = dbOpenHelper.getWritableDatabase();
    }

    /**
     * 添加记录
     */
    public long save(InfoBean bean) {
        long insertRowId = -1;
        try {
            ContentValues contentValues = new ContentValues();
            contentValues.put(SQLHelper.KEY_NAME, bean.getName());
            contentValues.put(SQLHelper.KEY_UNIT, bean.getUnit());
            contentValues.put(SQLHelper.KEY_DEPT, bean.getDept());
            contentValues.put(SQLHelper.KEY_TITLES, bean.getTitleString());
            contentValues.put(SQLHelper.KEY_OFFICEPHONES, bean.getOfficePhoneString());
            contentValues.put(SQLHelper.KEY_HOMEPHONES, bean.getHomePhoneString());
            contentValues.put(SQLHelper.KEY_MOBILEPHONES, bean.getMobilePhoneString());
            contentValues.put(SQLHelper.KEY_ADDRESSES, bean.getAddressString());
            contentValues.put(SQLHelper.KEY_QQS, bean.getQQString());
            contentValues.put(SQLHelper.KEY_WEIXINS, bean.getWeixinString());
            contentValues.put(SQLHelper.KEY_EMAILS, bean.getEmailString());
            contentValues.put(SQLHelper.KEY_TEAMS, bean.getTeamString());

            db.beginTransaction();// 开启事务
            insertRowId = db.insert(SQLHelper.KEY_TABLE, null, contentValues);
            db.setTransactionSuccessful();//设置提交标志
        } finally {
            db.endTransaction();// 结束事务,有两种情况：commit,rollback,
            // 事务的提交或回滚是由事务的标志决定的,如果事务的标志为True，事务就会提交，否侧回滚,默认情况下事务的标志为False
        }
        return insertRowId;
    }

    /**
     * 添加记录列表
     */
    public ArrayList<Long> saveInfoBeans(ArrayList<InfoBean> infoBeans) {
        ArrayList<Long> longs = new ArrayList<>();
        for (InfoBean bean : infoBeans) {
            long save = save(bean);
            longs.add(save);
        }
        return longs;
    }


    public InfoBean findInfoBeanById(int _id) {
        String selection = SQLHelper.KEY_ID + "=?";
        String[] selectionArgs = new String[]{_id + ""};
        String limit = "1";
        Cursor cursor = db.query(SQLHelper.KEY_TABLE, SQLHelper.columns,
                selection, selectionArgs, null, null, null, limit);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        cursor.moveToFirst();
        // 封装 InfoBean
        InfoBean bean = new InfoBean();

        bean.set_id(cursor.getInt(cursor.getColumnIndex(SQLHelper.KEY_ID)));
        bean.setName(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_NAME)));
        bean.setUnit(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_UNIT)));
        bean.setDept(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_DEPT)));

        bean.setOfficePhones(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_OFFICEPHONES))));
        bean.setHomePhones(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_HOMEPHONES))));
        bean.setMobilePhones(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_MOBILEPHONES))));
        bean.setAddresses(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_ADDRESSES))));
        bean.setQQs(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_QQS))));
        bean.setWeixins(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_WEIXINS))));
        bean.setEmails(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_EMAILS))));
        bean.setTeams(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_TEAMS))));

        cursor.close();
        return bean;
    }

    private ArrayList<String> setBean(String beanString) {
        if (!TextUtils.isEmpty(beanString)) {
            String[] split = beanString.split(" ");
            ArrayList<String> temp = new ArrayList<>();
            for (int i = 0; i < split.length; i++) {
                temp.add(split[i]);
            }
            return temp;
        }
        return null;
    }


    /**
     * 条件查询
     *
     * @param selection
     * @param selectionArgs
     * @return
     */
    public ArrayList<InfoBean> findInfoBeanListByCondition(String selection, String[] selectionArgs) {
        Cursor cursor = db.query(SQLHelper.KEY_TABLE, SQLHelper.columns,
                selection, selectionArgs, null, null, null, null);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        ArrayList<InfoBean> beans = new ArrayList<>();
        InfoBean bean;
        while (cursor.moveToNext()) {
            // 逐一封装
            bean = new InfoBean();

            bean.set_id(cursor.getInt(cursor.getColumnIndex(SQLHelper.KEY_ID)));
            bean.setName(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_NAME)));
            bean.setUnit(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_UNIT)));
            bean.setDept(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_DEPT)));

            bean.setOfficePhones(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_OFFICEPHONES))));
            bean.setHomePhones(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_HOMEPHONES))));
            bean.setMobilePhones(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_MOBILEPHONES))));
            bean.setAddresses(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_ADDRESSES))));
            bean.setQQs(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_QQS))));
            bean.setWeixins(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_WEIXINS))));
            bean.setEmails(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_EMAILS))));
            bean.setTeams(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_TEAMS))));

            beans.add(bean);
        }
        cursor.close();
        return beans;
    }

    // 查询所有的学校
    public LinkedHashMap<String, String> findIdUnitMap() {

        Cursor cursor = db.rawQuery("select _id,name,unit,dept from workmate where name is null and dept = '校长室' ", null);

//        Cursor cursor = db.query(true, SQLHelper.KEY_TABLE, new String[]{SQLHelper.KEY_ID, SQLHelper.KEY_UNIT},
//                "name = ? and dept = ?", new String[]{"","校长室"},
//                null, null, null, null);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }

        LinkedHashMap<String, String> idUnitMap = new LinkedHashMap<>();
        while (cursor.moveToNext()) {
            idUnitMap.put(cursor.getInt(cursor.getColumnIndex(SQLHelper.KEY_ID)) + "",
                    cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_UNIT))
            );
        }
        cursor.close();
        return idUnitMap;
    }


    // 某一学校内，所有的部门
    public LinkedHashMap<String, String> findIdDeptMap(String unit) {
        Cursor cursor = db.rawQuery("select _id,name,unit,dept from workmate where name is null and unit=?", new String[]{unit});

//        Cursor cursor = db.query(true, SQLHelper.KEY_TABLE, new String[]{SQLHelper.KEY_ID,SQLHelper.KEY_DEPT},
//                "unit=?", new String[]{unit}, null, null, "_id desc", null);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        LinkedHashMap<String, String> idDeptMap = new LinkedHashMap<>();
        while (cursor.moveToNext()) {
            idDeptMap.put(
                    cursor.getInt(cursor.getColumnIndex(SQLHelper.KEY_ID)) + "",
                    cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_DEPT))
            );
        }
        cursor.close();
        return idDeptMap;
    }


    // 某一学校内，所有的部门 之内 所有的人员
    public LinkedHashMap<String, String> findIdNameMap(String unit, String dept) {

        Cursor cursor = db.rawQuery("select _id,name,unit,dept from workmate where name is not null and unit=? and dept=?", new String[]{unit, dept});


//        Cursor cursor = db.query(true, SQLHelper.KEY_TABLE, new String[]{SQLHelper.KEY_ID,SQLHelper.KEY_NAME},
//                "unit=? and dept=? and name!=?", new String[]{unit, dept, ""},
//                null, null, "_id desc", null);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        LinkedHashMap<String, String> idNameMap = new LinkedHashMap<>();
        while (cursor.moveToNext()) {
            idNameMap.put(
                    cursor.getInt(cursor.getColumnIndex(SQLHelper.KEY_ID)) + "",
                    cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_NAME))
            );
        }
        cursor.close();
        return idNameMap;
    }


    public Cursor findInfoBeanCursor() {
        Cursor cursor = db.query(true, SQLHelper.KEY_TABLE, SQLHelper.columns,
                null, null, null, null, null, null);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        return cursor;
    }


    public Cursor findInfoBeanCursorByCondition(String selection, String[] selectionArgs) {
        Cursor cursor = db.query(SQLHelper.KEY_TABLE, SQLHelper.columns,
                selection, selectionArgs, null, null, null, null);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        return cursor;
    }


    public ArrayList<InfoBean> findInfoBeanList() {
        Cursor cursor = db.query(true, SQLHelper.KEY_TABLE, SQLHelper.columns,
                null, null, null, null, null, null);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        ArrayList<InfoBean> beans = new ArrayList<>();
        InfoBean bean;
        while (cursor.moveToNext()) {
            // 逐一封装
            bean = new InfoBean();

            bean.set_id(cursor.getInt(cursor.getColumnIndex(SQLHelper.KEY_ID)));
            bean.setName(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_NAME)));
            bean.setUnit(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_UNIT)));
            bean.setDept(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_DEPT)));

            bean.setOfficePhones(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_OFFICEPHONES))));
            bean.setHomePhones(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_HOMEPHONES))));
            bean.setMobilePhones(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_MOBILEPHONES))));
            bean.setAddresses(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_ADDRESSES))));
            bean.setQQs(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_QQS))));
            bean.setWeixins(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_WEIXINS))));
            bean.setEmails(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_EMAILS))));
            bean.setTeams(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_TEAMS))));

            beans.add(bean);
        }
        cursor.close();
        return beans;
    }


    /**
     * 查询 某一字段,  返回 这一字段的列表
     *
     * @param field
     * @return
     */
    public ArrayList<String> findInfoBeanFieldList(String field) {
        Cursor cursor = db.query(true, SQLHelper.KEY_TABLE, new String[]{field},
                null, null, null, null, null, null);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        ArrayList<String> fieldList = new ArrayList<>();
        while (cursor.moveToNext()) {
            fieldList.add(cursor.getString(cursor.getColumnIndex(field)));
        }
        cursor.close();
        return fieldList;
    }


    /**
     * 删除记录
     *
     * @param _id 记录ID
     */
    public int delete(int _id) {
        String whereClause = SQLHelper.KEY_ID + "=?";
        String[] whereArgs = new String[]{_id + ""};
        int deleteRowId = db.delete(SQLHelper.KEY_TABLE, whereClause, whereArgs);
        return deleteRowId;
    }


    /**
     * 更新记录
     */
    public void updateOrSave(InfoBean newBean) {
        if (newBean == null) {
            return;
        }
        InfoBean oldBean = findInfoBeanById(newBean.get_id());
        //if (oldBean == null) {
        if (newBean.get_id() == 0) {//新infoBean没有_id
            save(newBean);
            return;
        }
        // 更新操作
        db.beginTransaction();// 开启事务
        try {
            //  : 2018/12/27
            ContentValues contentValues = new ContentValues();

            contentValues.put(SQLHelper.KEY_NAME, newBean.getName());
            contentValues.put(SQLHelper.KEY_UNIT, newBean.getUnit());
            contentValues.put(SQLHelper.KEY_DEPT, newBean.getDept());
            contentValues.put(SQLHelper.KEY_TITLES, newBean.getTitleString());
            contentValues.put(SQLHelper.KEY_OFFICEPHONES, newBean.getOfficePhoneString());
            contentValues.put(SQLHelper.KEY_HOMEPHONES, newBean.getHomePhoneString());
            contentValues.put(SQLHelper.KEY_MOBILEPHONES, newBean.getMobilePhoneString());
            contentValues.put(SQLHelper.KEY_ADDRESSES, newBean.getAddressString());
            contentValues.put(SQLHelper.KEY_QQS, newBean.getQQString());
            contentValues.put(SQLHelper.KEY_WEIXINS, newBean.getWeixinString());
            contentValues.put(SQLHelper.KEY_EMAILS, newBean.getEmailString());
            contentValues.put(SQLHelper.KEY_TEAMS, newBean.getTeamString());

            String whereClause = SQLHelper.KEY_ID + "=?";
            String[] whereArgs = new String[]{oldBean.get_id() + ""};
            db.update(SQLHelper.KEY_TABLE, contentValues, whereClause, whereArgs);
            db.setTransactionSuccessful();// 设置事务的标志为True
        } finally {
            db.endTransaction();// 结束事务,有两种情况：commit,rollback,
            // 事务的提交或回滚是由事务的标志决定的,如果事务的标志为True，事务就会提交，否侧回滚,默认情况下事务的标志为False
        }
    }


    /**
     * 分页获取记录
     *
     * @param offset    跳过前面多少条记录
     * @param maxResult 每页获取多少条记录
     * @return
     */
    public ArrayList<InfoBean> findInfoBeanListPagination(int offset, int maxResult) {
        ArrayList<InfoBean> beans = new ArrayList<>();
        String limit = offset + "," + maxResult;
        Cursor cursor = db.query(true, SQLHelper.KEY_TABLE, SQLHelper.columns,
                null, null, null, null, null, limit);
        if (cursor == null || cursor.getCount() == 0) {
            return null;
        }
        InfoBean bean;
        while (cursor.moveToNext()) {
            // 逐一封装
            bean = new InfoBean();

            bean.set_id(cursor.getInt(cursor.getColumnIndex(SQLHelper.KEY_ID)));
            bean.setName(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_NAME)));
            bean.setUnit(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_UNIT)));
            bean.setDept(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_DEPT)));

            bean.setOfficePhones(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_OFFICEPHONES))));
            bean.setHomePhones(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_HOMEPHONES))));
            bean.setMobilePhones(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_MOBILEPHONES))));
            bean.setAddresses(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_ADDRESSES))));
            bean.setQQs(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_QQS))));
            bean.setWeixins(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_WEIXINS))));
            bean.setEmails(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_EMAILS))));
            bean.setTeams(setBean(cursor.getString(cursor.getColumnIndex(SQLHelper.KEY_TEAMS))));

            beans.add(bean);
        }
        cursor.close();
        return beans;
    }

    /**
     * 获取记录总数
     *
     * @return
     */
    public long getCount() {
        String[] fs = new String[]{SQLHelper.KEY_ID};
        Cursor cursor = db.query(true, SQLHelper.KEY_TABLE, fs,
                null, null, null, null, null, null);
        if (cursor == null) {
            return 0;
        }
        return cursor.getCount();
    }


}
